package pers.hxy.lookweather.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;

import java.io.BufferedReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import pers.hxy.lookweather.utils.AssetUtil;
import pers.hxy.lookweather.utils.DateUtil;
import pers.hxy.lookweather.utils.SpUtil;
import pers.hxy.lookweather.utils.StringUtil;

import static pers.hxy.lookweather.db.ChinaCityConfig.*;
import static pers.hxy.lookweather.db.ChinaCityConfig.CHINA_CITY_LIST_INDEX.*;
import static pers.hxy.lookweather.db.Config.DB_FILE_NAME;

public class ChinaCitySqliteHelper extends SQLiteOpenHelper {
    private static final String TAG = "ChinaCitySqliteHelper";

    private Context context;

    public ChinaCitySqliteHelper(@Nullable Context context) {
        super(context, DB_FILE_NAME, null, 1);
        this.context = context;
        Log.d(TAG, "constructor");
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d(TAG, "onCreate");
        String sql = AssetUtil.readSqlFile(this.context, CHINA_CITY_LIST_SQL_FILE);
        Log.d(TAG, "sql: " + sql);
        if (StringUtil.isNotNullOrEmpty(sql)) {
            db.execSQL(sql);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    @Override
    public synchronized void close() {
        super.close();
        context = null;
        Log.d(TAG, "close");
    }

    /**
     *
     * @return 一级行政区，即所有的省
     */
    public List<String> getAdm1_Name_ZH() {
        List<String> provinces  = new ArrayList<>(40);
        final String key = "all-provinces";
        final char sep = ';';
        String s = SpUtil.getString(context, key);
        if (s == null) {
            try (Cursor cursor = getReadableDatabase().query(true, CHINA_CITY_LIST_TABLE_NAME,
                    new String[]{Adm1_Name_ZH.name()},
                    null, null, null, null, null, null)) {
                while (cursor.moveToNext()) {
                    provinces.add(cursor.getString(0));
                }
            }
            SpUtil.putString(context, key, String.join(String.valueOf(sep), provinces));
            Log.d(TAG, "getAdm1_Name_ZH: get provinces from database");
        } else {
            StringUtil.split(s, sep, provinces);
            Log.d(TAG, "getAdm1_Name_ZH: get provinces from shared preferences");
        }
        return provinces;
    }

    /**
     *
     * @param adm1_name_zh 一级行政区
     * @return 一级行政区的所有二级行政区
     */
    public List<String> getAdm2_Name_ZH(String adm1_name_zh) {
        Log.d(TAG, "getAdm2_Name_ZH");
        List<String> names = new ArrayList<>(200);
        try (Cursor cursor = getReadableDatabase().query(true, CHINA_CITY_LIST_TABLE_NAME,
                new String[]{Adm2_Name_ZH.name()},
                Adm1_Name_ZH.name() + " = ? ", new String[]{adm1_name_zh},
                null, null, null, null)) {
            while (cursor.moveToNext()) {
                names.add(cursor.getString(0));
            }
        }
        return names;
    }

    /**
     *
     * @param adm1_name_zh 一级行政区
     * @param adm2_name_zh 二级行政区
     * @return 所有三级行政区
     */
    public List<String> getLocation_Name_ZH(String adm1_name_zh, String adm2_name_zh) {
        Log.d(TAG, "getLocation_Name_ZH");
        List<String> names = new ArrayList<>(200);
        try (Cursor cursor = getReadableDatabase().query(CHINA_CITY_LIST_TABLE_NAME,
                new String[]{Location_Name_ZH.name()},
                Adm1_Name_ZH.name() + " = ? " + " AND " + Adm2_Name_ZH.name() + " = ? ",
                new String[]{adm1_name_zh, adm2_name_zh},
                null, null, null, null)) {
            while (cursor.moveToNext()) {
                names.add(cursor.getString(0));
            }
        }
        return names;
    }

    /**
     *
     * @param adm1_name_zh 一级行政区
     * @param adm2_name_zh 二级行政区
     * @param location_name_zh 三级行政区
     * @return 由三级行政区唯一确定的id
     */
    public String getLocation_ID(String adm1_name_zh, String adm2_name_zh, String location_name_zh) {
        String id;
        try (Cursor cursor = getReadableDatabase().query(CHINA_CITY_LIST_TABLE_NAME,
                new String[]{Location_ID.name()},
                Adm1_Name_ZH.name() + " = ? " + " AND "
                        + Adm2_Name_ZH.name() + " = ? " + " AND "
                        + Location_Name_ZH.name() + " = ? ",
                new String[]{adm1_name_zh, adm2_name_zh, location_name_zh},
                null, null, null, null)) {
            cursor.moveToFirst();
            id = cursor.getString(0);
        }
        Log.d(TAG, "getLocation_ID: " + id);
        return id;
    }

    /**
     * 将城市列表读入数据库
     * @return 是否读取成功
     */
    public boolean readChinaCityListCsvToSqliteDatabase() {
        Log.d(TAG, "readChinaCityListCsvToSqliteDatabase");
        String version = SpUtil.getString(context, SP_CHINA_CITY_LIST_VERSION_INFO);
        if (version != null) { // 已经保存过了
            Log.d(TAG, "already saved: " + version);
            return true;
        }
        try (BufferedReader csvReader = AssetUtil.readCSV(context, CHINA_CITY_LIST_CSV_FILE)) {
            if (csvReader == null) {
                return false;
            }
            SQLiteDatabase db = getWritableDatabase();
            db.beginTransaction();
            try {
                long startTime = System.nanoTime();
                Log.d(TAG, "start read csv to db: " + startTime);

                String info = csvReader.readLine();
                version = info.substring(0, info.indexOf(','));

                String head = csvReader.readLine();

                String line;
                int count = 0;
                ContentValues contentValues = new ContentValues(); // 只用一个ContentValues对象
                List<String> columns = new ArrayList<>(CHINA_CITY_LIST_COLUMN_COUNT); // 保存分割后的内容
                while ((line = csvReader.readLine()) != null) {
                    count++;
                    StringUtil.split(line, ',', columns);
                    for (int i = 0; i < CHINA_CITY_LIST_COLUMN_COUNT; i++) {
                        contentValues.put(CHINA_CITY_LIST_COLUMN_NAMES[i], columns.get(i));
                    }
                    db.insertOrThrow(CHINA_CITY_LIST_TABLE_NAME, null, contentValues);
                    // 用完后clear，重用资源，防止重复分配新对象
                    contentValues.clear();
                    columns.clear();
                }

                db.setTransactionSuccessful();

                // 操作成功，保存信息，之后就不用重复操作
                SpUtil.putString(context, SP_CHINA_CITY_LIST_VERSION_INFO, version);
                SpUtil.putString(context, "read-csv-to-db-datetime", DateUtil.getCurrentTime("yyyy-MM-dd HH:mm:ss"));

                long endTime = System.nanoTime();
                long estimatedTime = endTime - startTime;
                double estimatedTimeMills = estimatedTime / 1e6;
                Log.d(TAG, "end read csv to db: " + endTime);
                Log.d(TAG, "use time " + estimatedTimeMills + " mill seconds");
                Log.d(TAG, "total " + count + " insertions");
                SpUtil.putString(context, "read-csv-to-db-use-time-mill-seconds", String.valueOf(estimatedTimeMills));

                return true;
            } catch (Exception e) {
                Log.d(TAG, e.toString());
            } finally {
                db.endTransaction();
            }
        } catch (IOException e) {
            Log.d(TAG, e.toString());
        }
        return false;
    }
}
